package com.manager.service.message;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.common.util.TranslateHelper;
import org.jorm.Session;

import com.build.SessionBuilder; 
import com.web.model.Message; 
import com.web.model.Message;
import com.web.model.News; 
import com.web.model.dao.MessageDao;  
import com.web.model.dao.impl.MessageDaoImpl; 
import com.web.model.vo.query.MessageQuery;
import com.web.tag.bean.PageModel;

public class MessageService {
	
	public List<Message> findList(PageModel model, MessageQuery query) {
		List<Message> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			MessageDao messageDao = new MessageDaoImpl();
			String whereSql = " where 1=1 ";
			if (query.getMsgid() != null && query.getMsgid()> 0) {
				whereSql += " and " + Message.COLUMN_MSGID+ " = " + query.getMsgid();
			}
			if (query.getTitle() != null && !query.getTitle().trim().isEmpty()) {
				whereSql += " and " + Message.COLUMN_TITLE + " like '%" + query.getTitle() + "%'";
			}
			String sql = "select " + Message.COLUMN_ADDTIME + "," + Message.COLUMN_CONTENT + "," + Message.COLUMN_MSGID + "," + Message.COLUMN_STATE
						+ "," + Message.COLUMN_TITLE + " ," + Message.COLUMN_USERID +"," + Message.COLUMN_USERIP +"  from "
					+ Message.TABLE_NAME + " " + whereSql + " order by " + Message.COLUMN_ADDTIME + " desc";
			//System.out.println(sql);
			if (model != null) {
				try {
					model.setRecodeZong(messageDao.getResultCount("select count(*) as ct from " + Message.TABLE_NAME + " " + whereSql, 1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= " + row_x + ") where rownum_ > " + row_y;
			}

			list =getList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	public List<Message> findList() {
		List<Message> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			MessageDao messageDao = new MessageDaoImpl();
			String sql = "select  * from " + Message.TABLE_NAME + " order by "
					+ Message.COLUMN_ADDTIME + " ";
			list = messageDao.queryForList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public int save(Message message) {
		MessageDao messageDao = new MessageDaoImpl();
		int id = 0;
		try {
			
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
		
			PreparedStatement ps =null;
			ResultSet rs = null;
			if(message.getMsgid()==null||message.getMsgid()==0){
				 
					String sqlMax = " select max("+Message.COLUMN_MSGID+")+1 from   "+Message.TABLE_NAME;
					ps = conn.prepareStatement(sqlMax);
					rs = ps.executeQuery();
					
					while (rs.next())
						id = rs.getInt(1);
					message.setMsgid(id);
			} 
			String sql = "insert into "+Message.TABLE_NAME+"(TITLE,CONTENT,ADDTIME,USERID,USERIP,state,MSGID) " 
			+" values (?,?,sysdate,?,?,?,?)";
			ps = conn.prepareStatement(sql);

			ps.setString(1, message.getTitle());
			ps.setString(2,message.getContent()); 
			ps.setInt(3, message.getUserid());
			ps.setString(4,message.getUserip());
			ps.setInt(5,TranslateHelper.parseInteger(message.getState())); 
			ps.setInt(6,TranslateHelper.parseInteger(message.getMsgid()));  
			 
			ps.executeUpdate(); 
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;

	}
	public int update(Message message) {
		MessageDao messageDao = new MessageDaoImpl();
		int id = 0;
		try {
			
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection(); 
			PreparedStatement ps =null;
			ResultSet rs = null;
			 
			String sql = "update "+Message.TABLE_NAME+" set title=?, content=?,userid=?,userip=?,state=?  " +
					"  where msgid=? ";
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, message.getTitle());
			ps.setString(2,message.getContent()); 
			ps.setInt(3, message.getUserid());
			ps.setString(4,message.getUserip());
			ps.setInt(5,TranslateHelper.parseInteger(message.getState())); 
			ps.setInt(6,TranslateHelper.parseInteger(message.getMsgid()));  
			ps.executeUpdate(); 
			  
			ps.close();
			session.close();
			id=0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;

	}
	public int saveOrupdate(Message message) { 
		int id=message.getMsgid();
		if (message.getMsgid() == null || message.getMsgid() <= 0) {
			 id=save(message);
		} else {
			 update(message);
		}
		return id;

	}
	public List<Message> getList(String  sql) {
		
		List list=new ArrayList();
		Message obj = new Message();
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection(); 
			PreparedStatement ps = conn.prepareStatement(sql);
			 
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				obj = new Message();
				obj.setAddtime(rs.getTimestamp(Message.COLUMN_ADDTIME));
				obj.setMsgid(rs.getInt(Message.COLUMN_MSGID));
				obj.setContent(rs.getString(Message.COLUMN_CONTENT));
				obj.setState(rs.getInt(Message.COLUMN_STATE));
				obj.setTitle(rs.getString(Message.COLUMN_TITLE));
				obj.setUserid(rs.getInt(Message.COLUMN_USERID));
				obj.setUserip(rs.getString(Message.COLUMN_USERIP));
				list.add(obj);
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	

	public Message getById(int id) {
		Message obj = new Message();
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + Message.TABLE_NAME + " where "
					+ Message.COLUMN_MSGID + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new Message();
				obj.setAddtime(rs.getTimestamp(Message.COLUMN_ADDTIME));
				obj.setMsgid(rs.getInt(Message.COLUMN_MSGID));
				obj.setContent(rs.getString(Message.COLUMN_CONTENT));
				obj.setState(rs.getInt(Message.COLUMN_STATE));
				obj.setTitle(rs.getString(Message.COLUMN_TITLE));
				obj.setUserid(rs.getInt(Message.COLUMN_USERID));
				obj.setUserip(rs.getString(Message.COLUMN_USERIP));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
	public void delete(int id) {
		try {
			Session session = SessionBuilder.oracleSession();
			String sql = "delete from " + Message.TABLE_NAME + " where " + Message.COLUMN_MSGID + " = ?";
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
